package com.qf.index;

import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ZipUtil;
import cn.hutool.crypto.SecureUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;

public class transAb {

    /**
     * 翻译中英文摘要，这个方法是首次翻译，这里的摘要指的是摘要中的背景、方法、结果、结论，生产中不使用这个方法，生产中的方法在WebSite。
     * 贾新志 2025.03.30
     */

    public static void main(String[] args) throws SQLException {

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        System.out.println(format.format(new Date()) + " 程序开始了~~~");

        String newDataName = "scholar_25_02.";
        String abstractTransTable = newDataName + "basic_abstract_trans";
        String summaryFinalTable = newDataName + "unified_search_summary_0321_abstract";

        int onePageNum = 1000;
        String limit = "";
//        String limit = " limit 1";

        // 将总表中的背景、方法、结果、结论，中英文共8个字段，以及一些辅助字段，转换为需要翻译的字符串存到abstractTransTable表中。
        saveAbstractTransTable(summaryFinalTable, "id", "aid`,`discussion`,`results`,`methods`,`background`,`cn_discussion`,`cn_results`,`cn_methods`,`cn_background`,`pmId`,`ut`,`year", limit, abstractTransTable);

        // 分段翻译摘要，本方法可以使用d1、d2、d3、d4，分成4段，反复跑，反复跑时上面的方法saveAbstractTransTable需要注掉。
        transAbstract(abstractTransTable, "id", "original`,`trans", 0, 100);


        // 翻译完之后，再作测试。
        Map<String, byte[]> abstractTransMap = WebSite.getTitleTransMap(abstractTransTable, "id", "original`,`trans", limit);
        System.out.println("\033[31;1m" + format.format(new Date()) + " ⒇ 获取已经翻译的摘要结束，下面开始进行有道翻译。abstractTransMap.size()：" + abstractTransMap.size() + Utils.printMemoryStr() + "\033[0m");
        updateAbstract(summaryFinalTable, "id", "aid`,`discussion`,`results`,`methods`,`background`,`cn_discussion`,`cn_results`,`cn_methods`,`cn_background`,`pmId`,`ut`,`year", abstractTransMap, abstractTransTable);
        abstractTransMap.clear();

    }

    public static void updateAbstract(String summaryFinalTable, String autoId, String field, Map<String, byte[]> abstractTransMap, String abstractTransTable) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int onePageNum = 10000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + summaryFinalTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + summaryFinalTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                System.out.println("\033[31;1m" + format.format(new Date()) + " 摘要翻译：" + startId + " 表名：" + summaryFinalTable + Utils.printMemoryStr() + "\033[0m");
                for (Entity entry : tableData) {
                    int id = Utils.getIntField(entry, autoId);
                    int aid = Utils.getIntField(entry, "aid");
                    int pmId = Utils.getIntField(entry, "pmId");
                    int year = Utils.getIntField(entry, "year");
                    String ut = Utils.getStrField(entry, "ut");
                    try {
                        Db.use().update(
                                Entity.create()
                                        .set("discussion", transField(entry.getStr("discussion"), abstractTransMap, ut, pmId, aid, year, abstractTransTable))
                                        .set("results", transField(entry.getStr("results"), abstractTransMap, ut, pmId, aid, year, abstractTransTable))
                                        .set("methods", transField(entry.getStr("methods"), abstractTransMap, ut, pmId, aid, year, abstractTransTable))
                                        .set("background", transField(entry.getStr("background"), abstractTransMap, ut, pmId, aid, year, abstractTransTable))
                                        .set("cn_discussion", transField(entry.getStr("cn_discussion"), abstractTransMap, ut, pmId, aid, year, abstractTransTable))
                                        .set("cn_results", transField(entry.getStr("cn_results"), abstractTransMap, ut, pmId, aid, year, abstractTransTable))
                                        .set("cn_methods", transField(entry.getStr("cn_methods"), abstractTransMap, ut, pmId, aid, year, abstractTransTable))
                                        .set("cn_background", transField(entry.getStr("cn_background"), abstractTransMap, ut, pmId, aid, year, abstractTransTable)),
                                Entity.create(summaryFinalTable).set(autoId, id));
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    public static String transField(String field, Map<String, byte[]> abstractTransMap, String ut, int pmId, int aid, int year, String abstractTransTable) {
        List<En.Trans> result = new ArrayList<>();
        if (null != field && field.contains("label")) {
            List<En.Trans> transList = JSONObject.parseArray(field, En.Trans.class);
            for (En.Trans trans : transList) {
                String en = trans.getEn();
                String cn = trans.getCn();
                String label = trans.getLabel();
                if (en.length() > 1 && label.length() > 1 && cn.isEmpty()) {
                    String md5 = SecureUtil.md5(en).toLowerCase();
                    String translation = "";
                    if (abstractTransMap.containsKey(md5)) {
                        translation = ZipUtil.unGzip(abstractTransMap.get(md5), "utf-8");
                    } else {
                        boolean isCn = Utils.isContainChinese(en);
                        translation = "ĺļ" + (isCn ? TransApi.youDaoCnToEnTrans(en) : TransApi.youDaoEnToCnTrans(en));
                        if (translation.length() > 3) {
                            insertTransTable(ut, pmId, label, en, aid, year, abstractTransTable, translation, isCn ? 1 : 2);
                        }
                    }
                    if (translation.length() > 3) {
                        En.Trans tran = new En.Trans();
                        tran.setEn(trans.getEn());
                        tran.setCn(translation);
                        tran.setLabel(label);
                        result.add(tran);
                    } else {
                        result.add(trans);
                    }
                } else if (cn.length() > 1 && label.length() > 1 && en.isEmpty()) {
                    String md5 = SecureUtil.md5(cn).toLowerCase();
                    String translation = "";
                    if (abstractTransMap.containsKey(md5)) {
                        translation = ZipUtil.unGzip(abstractTransMap.get(md5), "utf-8");
                    } else {
                        boolean isCn = Utils.isContainChinese(cn);
                        translation = "ĺļ" + (isCn ? TransApi.youDaoCnToEnTrans(cn) : TransApi.youDaoEnToCnTrans(cn));
                        if (translation.length() > 3) {
                            insertTransTable(ut, pmId, label, cn, aid, year, abstractTransTable, translation, isCn ? 1 : 2);
                        }
                    }
                    if (translation.length() > 3) {
                        En.Trans tran = new En.Trans();
                        tran.setEn(translation);
                        tran.setCn(trans.getCn());
                        tran.setLabel(label);
                        result.add(tran);
                    } else {
                        result.add(trans);
                    }
                } else {
                    result.add(trans);
                }
            }
        }
        return JSONUtil.toJsonStr(result);
    }

    public static void transAbstract(String abstractTransTable, String autoId, String field, int min, int max) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int onePageNum = 10000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + abstractTransTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + abstractTransTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                System.out.println("\033[31;1m" + format.format(new Date()) + " 摘要翻译：" + startId + " 表名：" + abstractTransTable + Utils.printMemoryStr() + "\033[0m");
                for (Entity entry : tableData) {
                    int id = Utils.getIntField(entry, autoId);
                    int end = id > 10 ? Integer.parseInt(Integer.toString(id).substring(Integer.toString(id).length() - 2)) : id;
                    String original = Utils.getStrField(entry, "original");
                    String trans = Utils.getStrField(entry, "trans");
                    if (end >= min && end <= max && original.length() > 1 && trans.isEmpty()) {
                        boolean isCn = Utils.isContainChinese(original);
                        trans = isCn ? TransApi.youDaoCnToEnTrans(original) : TransApi.youDaoEnToCnTrans(original);
                        if (trans.length() > 1) {
                            try {
                                Db.use().update(Entity.create().set("trans", ("ĺļ" + trans)).set("type", isCn ? 1 : 2), Entity.create(abstractTransTable).set(autoId, id));
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
    }

    public static void saveAbstractTransTable(String summaryFinalTable, String autoId, String field, String limit, String abstractTransTable) throws SQLException {
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + summaryFinalTable);
        Db.use().execute("truncate table " + abstractTransTable);
        int onePageNum = 1000;
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + summaryFinalTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            tableList.parallelStream().forEach(startId -> {
                try {
                    List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + summaryFinalTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
                    for (Entity entry : tableData) {
                        int aid = Utils.getIntField(entry, "aid");
                        int pmId = Utils.getIntField(entry, "pmId");
                        int year = Utils.getIntField(entry, "year");
                        String ut = Utils.getStrField(entry, "ut");
                        insertField(ut, pmId, entry.getStr("discussion"), aid, year, abstractTransTable);
                        insertField(ut, pmId, entry.getStr("results"), aid, year, abstractTransTable);
                        insertField(ut, pmId, entry.getStr("methods"), aid, year, abstractTransTable);
                        insertField(ut, pmId, entry.getStr("background"), aid, year, abstractTransTable);
                        insertField(ut, pmId, entry.getStr("cn_discussion"), aid, year, abstractTransTable);
                        insertField(ut, pmId, entry.getStr("cn_results"), aid, year, abstractTransTable);
                        insertField(ut, pmId, entry.getStr("cn_methods"), aid, year, abstractTransTable);
                        insertField(ut, pmId, entry.getStr("cn_background"), aid, year, abstractTransTable);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
        }
    }

    public static void insertField(String ut, int pmId, String field, int aid, int year, String abstractTransTable) {
        if (null != field && field.contains("label")) {
            List<En.Trans> transList = JSONObject.parseArray(field, En.Trans.class);
            for (En.Trans trans : transList) {
                String en = trans.getEn();
                String cn = trans.getCn();
                String label = trans.getLabel();
                if (en.length() > 1 && label.length() > 1 && cn.isEmpty()) {
                    insertTransTable(ut, pmId, label, en, aid, year, abstractTransTable, "", 0);
                }
                if (cn.length() > 1 && label.length() > 1 && en.isEmpty()) {
                    insertTransTable(ut, pmId, label, cn, aid, year, abstractTransTable, "", 0);
                }
            }
        }
    }

    public static void insertTransTable(String ut, int pmId, String label, String original, int aid, int year, String abstractTransTable, String trans, int type) {
        try {
            Db.use().insert(Entity.create(abstractTransTable).set("ut", ut).set("pm", pmId).set("aid", aid).set("label", label).set("original", original)
                    .set("original_len", original.length()).set("year", year).set("trans", trans).set("type", type).set("datetime", LocalDateTime.now()));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
